﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_GetBasicReference')
	BEGIN
		DROP  Procedure  [esf_sso].P_GetBasicReference
	END

GO

CREATE Procedure [esf_sso].P_GetBasicReference
	@pID						BIGINT = NULL
	,@pCode						VARCHAR (100) = NULL
	,@pName						VARCHAR (500) = NULL
	,@pBasicReferenceTypeID		INT	= NULL
AS

	DECLARE @msg VARCHAR (MAX)

	IF @pID IS NOT NULL
	BEGIN

		SELECT * FROM [esf_sso].VBasicReference WHERE ID = @pID

	END
	ELSE IF @pBasicReferenceTypeID IS NOT NULL
	BEGIN
		
		SELECT * FROM [esf_sso].VBasicReference 
		WHERE 
			(@pCode IS NULL OR Code = @pCode)
			AND (@pName IS NULL OR Name = @pName)
			AND BasicReferenceTypeID = @pBasicReferenceTypeID
		
	END
	ELSE
	BEGIN

		SET @msg = 'The basicreference provided does not exist!: ID = ' + COALESCE (CONVERT (VARCHAR (20), @pID), 'NULL') +
				   ', TypeID = ' + COALESCE (CONVERT (VARCHAR (20), @pBasicReferenceTypeID), 'NULL')

		RAISERROR (@msg, 16, 1)
	END	

GO